using System;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public class Common
    {
        
        private static string ConnectionString
        {
            get { return "Server=.;Initial Catalog=ManageSchool;Integrated Security=True"; }
        }

        /// <summary>
        /// Returns result set in DataTable given SP name
        /// </summary>
        /// <param name="SPName">SQL Stored Procedure Name</param>
        /// <param name="Parameters">SQL Parameters</param>
        /// <returns></returns>
        public static DataTable GetFromDataTable(string SPName, params SqlParameter[] Parameters)
        {
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            try
            {
                //Here we take index and PageSize from the argument.
                //index and PageSize value are generated from the category.aspx and passt
                //through the business logic along with SQL parameters.
                da.Fill(ds);
            }
            catch
            {
                cn.Close();
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// Returns result set in DataTable given SP name with paging capability
        /// </summary>
        /// <param name="SPName">SQL Stored Procedure Name</param>
        /// <param name="Parameters">SQL Parameters</param>
        /// <returns></returns>
        public static DataTable GetFromDataTableWithPaging(string SPName, int index, int PageSize,
                                                           params SqlParameter[] Parameters)
        {
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            try
            {
                //Here we take index and PageSize from the argument.
                //index and PageSize value are generated from the category.aspx and passt
                //through the business logic along with SQL parameters.
                da.Fill(ds, index, PageSize, "tb");
            }
            catch
            {
                cn.Close();
                throw;
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }

        /// <summary>
        /// Returns an IDataReader result from a specified stored procedure
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Array of SqlParameters</param>
        /// <returns></returns>
        public static IDataReader GetFromReader(string SPName, params SqlParameter[] Parameters)
        {
            IDataReader dr;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    
                    cmd.Parameters.Add(item);

            try
            {
                cn.Open();
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (true) cn.Close();
                throw;
            }

            return dr;
        }

        /// <summary>
        /// Returns string result from a specified stored procedure
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Array of SqlParameters</param>
        /// <returns></returns>
        public static string GetString(string SPName, params SqlParameter[] Parameters)
        {
            string output = "";
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);
            SqlDataReader dreader;

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            cn.Open();
            try
            {
                dreader = cmd.ExecuteReader();

                if (dreader.Read())
                    if (dreader.GetValue(0) != DBNull.Value)
                        output = dreader.GetString(0);

                dreader.Close();
            }
            catch
            {
                cn.Close();
                throw;
            }

            cn.Close();
            return output;
        }

        public static double GetDouble(string SPName, params SqlParameter[] Parameters)
        {
            double output = 0;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);
            SqlDataReader dreader;

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            cn.Open();
            try
            {
                dreader = cmd.ExecuteReader();

                if (dreader.Read())
                    if (dreader.GetValue(0) != DBNull.Value)
                        output = dreader.GetDouble(0);

                dreader.Close();
            }
            catch
            {
                cn.Close();
                throw;
            }

            cn.Close();
            

            return output;
        }

        /// <summary>
        /// Returns Int32 result from a specified stored procedure
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Array of SqlParameters</param>
        /// <returns></returns>
        public static int GetInt32(string SPName, params SqlParameter[] Parameters)
        {
            int output = 0;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            cn.Open();

            try
            {
                SqlDataReader dreader = cmd.ExecuteReader();
                if (dreader.Read())
                    if (dreader.GetValue(0) != DBNull.Value)
                        output = Convert.ToInt32(dreader.GetValue(0));

                dreader.Close();
            }
            catch
            {
                cn.Close();
                throw;
            }

            cn.Close();
            return output;
        }

        /// <summary>
        /// Returns Int32 scalar value from stored procedure
        /// </summary>
        /// <returns></returns>
        public static int GetIntScalarVal(string SPName)
        {
            int output;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            cn.Open();

            try
            {
                output = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch
            {
                cn.Close();
                throw;
            }

            cn.Close();
            return output;
        }

        /// <summary>
        /// Executes Insert Stored Procedure
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="Parameters">Array of SqlParameters</param>
        /// <returns>Returns 0 if successful. Otherwise returns 1.</returns>
        public static int Execute(string SPName, params SqlParameter[] Parameters)
        {
            int intErr = 0;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);

            cmd.CommandText = SPName;
            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)
                    cmd.Parameters.Add(item);

            cn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                intErr = 1;
                cn.Close();
            }
            cn.Close();
            return intErr;
        }

        /// <summary>
        /// getID
        /// </summary>
        /// <param name="tagfix"></param>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static string getID(string tagfix, string procName)
        {
            string ID = "";
            DataTable dt = GetFromDataTable(procName);
            if (dt.Rows.Count == 0)
            {
                ID = tagfix + "0001";
                return ID;
            }
            string temp = dt.Rows[0][0].ToString();
            temp = temp.Substring(tagfix.Length, (temp.Length - tagfix.Length));
            int autoNumber = Convert.ToInt32(temp);
            if (autoNumber < 9)
            {
                ID = tagfix + "000" + (autoNumber + 1);
            }
            if ((9 < autoNumber || autoNumber==9) && autoNumber < 99)
            {
                ID = tagfix + "00" + (autoNumber + 1);
            }
            if ((99 < autoNumber || autoNumber == 99) && autoNumber < 999)
            {
                ID = tagfix + "0" + (autoNumber + 1);
            }
            if ((999 < autoNumber || autoNumber == 999))
            {
                ID = tagfix + autoNumber;
            }
            return ID;
        }
 
        /// <summary>
        /// return datareader
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static SqlDataReader GetFromDataReader(string SPName, params SqlParameter[] Parameters)
        {
            SqlDataReader dr;
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SPName, cn);

            cmd.CommandType = CommandType.StoredProcedure;

            if (Parameters != null)
                foreach (SqlParameter item in Parameters)

                    cmd.Parameters.Add(item);

            try
            {
                cn.Open();
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                cn.Close();
                throw;
            }
            return dr;
        }
    }
}